CREATE TABLE IF NOT EXISTS itcast_dwd.itcast_clue_dwd (
   `id` STRING COMMENT '线索id',
   `customer_relationship_id` int COMMENT '客户关系id',
   `origin_type` STRING COMMENT '数据来源:0.线下；1.线上',
   `clue_state` STRING COMMENT '0:未知；1：新客户线索；2：旧客户线索',
   `deleted` STRING COMMENT '是否删除',
   `create_date_time` date COMMENT '创建时间',
   `hourinfo` STRING COMMENT '小时信息'
)
comment '客户申诉dwd表'
PARTITIONED BY(yearinfo STRING,monthinfo STRING,dayinfo STRING)
clustered by(customer_relationship_id) sorted by(customer_relationship_id) into 10 buckets
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orcfile
TBLPROPERTIES ('orc.compress'='SNAPPY','orc.create.index'='true','orc.bloom.filter.columns'='customer_relationship_id');

--分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;
--分桶
set hive.enforce.bucketing=true;
set hive.enforce.sorting=true;
set hive.optimize.bucketmapjoin = true;
set hive.auto.convert.sortmerge.join=true;
set hive.auto.convert.sortmerge.join.noconditionaltask=true;
--并行执行
set hive.exec.parallel=true;
set hive.exec.parallel.thread.number=8;

INSERT INTO itcast_dwd.itcast_clue_dwd PARTITION (yearinfo, monthinfo, dayinfo)
SELECT
    clue.id,
    nvl(clue.customer_relationship_id, -1) clue.customer_relationship_id,
    rs.origin_type,
    clue.clue_state,
    clue.deleted,
    clue.create_date_time,
    substr(clue.create_date_time, 12, 2) as hourinfo,
    substr(clue.create_date_time, 1, 4) as yearinfo,
    substr(clue.create_date_time, 6, 2) as monthinfo,
    substr(clue.create_date_time, 9, 2) as dayinfo
FROM itcast_ods.customer_clue clue
LEFT JOIN itcast_ods.customer_relationship rs on clue.customer_relationship_id = rs.id
where clue.clue_state is not null AND clue.deleted = 'false';



CREATE TABLE IF NOT EXISTS itcast_dwm.itcast_clue_dwm (
   `id` STRING COMMENT '线索id',
   `origin_type_stat` STRING COMMENT '数据来源:0.线下；1.线上',
   `for_new_user` STRING COMMENT '0:未知；1：新客户线索；2：旧客户线索',
   `hourinfo` STRING COMMENT '小时信息',
   `dayinfo`STRING COMMENT '天信息',
   `monthinfo` STRING COMMENT '月信息'
)
comment '客户申诉dwm表'
PARTITIONED BY(yearinfo STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orcfile
TBLPROPERTIES ('orc.compress'='SNAPPY');



INSERT into itcast_dwm.itcast_clue_dwm partition (yearinfo)
SELECT
    dwd.id,
    if(dwd.origin_type='NETSERVICE' or dwd.origin_type='PRESIGNUP', '1', '0') as origin_type_stat,
    if(dwd.clue_state='VALID_NEW_CLUES', 1, if(dwd.clue_state='VALID_PUBLIC_NEW_CLUE', 2, 0)) as for_new_user,
    dwd.hourinfo,
    dwd.dayinfo,
    dwd.monthinfo,
    dwd.yearinfo
from itcast_dwd.itcast_clue_dwd dwd
WHERE 
dwd.customer_relationship_id NOT IN
(
    SELECT a.customer_relationship_first_id from itcast_ods.customer_appeal a
    WHERE a.appeal_status = 1 and a.customer_relationship_first_id != 0
);



CREATE TABLE IF NOT EXISTS itcast_dws.itcast_clue_dws (
   `clue_nums` INT COMMENT '根据id聚合',
   `origin_type_stat` STRING COMMENT '数据来源:0.线下；1.线上',
   `for_new_user` STRING COMMENT '0:未知；1：新客户线索；2：旧客户线索',
   `hourinfo` STRING COMMENT '小时信息',
   `dayinfo`STRING COMMENT '天信息',
   `monthinfo` STRING COMMENT '月信息',
   `time_type` STRING COMMENT '聚合时间类型：1、按小时聚合；2、按天聚合；3、按周聚合；4、按月聚合；5、按年聚合；',
   `time_str` STRING COMMENT '时间明细'
)
comment '客户申诉app表'
PARTITIONED BY(yearinfo STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orcfile
TBLPROPERTIES ('orc.compress'='SNAPPY');




--小时
INSERT INTO itcast_dws.itcast_clue_dws PARTITION(yearinfo)
SELECT 
    count(id) as clue_nums,
    origin_type_stat, 
    for_new_user, 
    hourinfo, 
    dayinfo, 
    monthinfo, 
    '1' as time_type, 
    concat(dwm.yearinfo,'-',dwm.monthinfo,'-',dwm.dayinfo,' ', dwm.hourinfo) as time_str, 
    yearinfo
from itcast_dwm.itcast_clue_dwm dwm
GROUP BY dwm.yearinfo, dwm.monthinfo, dwm.dayinfo, dwm.hourinfo,dwm.origin_type_stat, dwm.for_new_user;

--天
INSERT INTO itcast_dws.itcast_clue_dws PARTITION(yearinfo)
SELECT 
    count(id) as clue_nums,
    origin_type_stat, 
    for_new_user, 
    '-1' as hourinfo, 
    dayinfo, 
    monthinfo, 
    '2' as time_type, 
    concat(dwm.yearinfo,'-',dwm.monthinfo,'-',dwm.dayinfo) as time_str, 
    yearinfo
from itcast_dwm.itcast_clue_dwm dwm
GROUP BY dwm.yearinfo, dwm.monthinfo, dwm.dayinfo, dwm.origin_type_stat, dwm.for_new_user;


--月
INSERT INTO itcast_dws.itcast_clue_dws PARTITION(yearinfo)
SELECT 
    count(id) as clue_nums,
    origin_type_stat, 
    for_new_user, 
    '-1' as hourinfo, 
    '-1' asdayinfo, 
    monthinfo, 
    '4' as time_type, 
    concat(dwm.yearinfo,'-',dwm.monthinfo) as time_str, 
    yearinfo
from itcast_dwm.itcast_clue_dwm dwm
GROUP BY dwm.yearinfo, dwm.monthinfo, dwm.origin_type_stat, dwm.for_new_user;



--年
INSERT INTO itcast_dws.itcast_clue_dws PARTITION(yearinfo)
SELECT 
    count(id) as clue_nums,
    origin_type_stat, 
    for_new_user, 
    '-1' as hourinfo, 
    '-1' as dayinfo, 
    '-1' as monthinfo, 
    '5' as time_type, 
    dwm.yearinfo as time_str, 
    yearinfo
from itcast_dwm.itcast_clue_dwm dwm
GROUP BY dwm.yearinfo, dwm.origin_type_stat, dwm.for_new_user;

